package light.head.framework.utils;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import light.head.constant.Sys;
import light.head.framework.entity.DBConnCfg;
import light.head.framework.entity.PageBean;

import org.dom4j.Document;
import org.dom4j.Element;

/**
 * @author 简航 创建日期 2009-08-20 13:58:00
 */
public class DatabaseUtils {

	private static Connection con;
	private static DBConnCfg hibernateCfi = null;
	
	public static Connection getConnection() {
		return con;
	}
	
	public static boolean createConnection(DBConnCfg dci) {
		return  createConnection(dci.getUname(), dci.getPassword(), dci.getDriver(), dci.getConStr());
	}

	/**
	 * 创建数据库连接
	 * @param 数据库登录名称
	 * @param 数据库登录密码
	 * @param 数据库驱动字符串
	 * @param 数据库连接字符串
	 * @return 是否连接成功
	 * @author 简航 创建日期 2009-08-20 15:00:52
	 */
	public static boolean createConnection(String uname, String pwd, String driverStr, String connectStr) {
		try {
			Class.forName(driverStr == null || "".equals(driverStr) ? "com.microsoft.sqlserver.jdbc.SQLServerDriver" : driverStr);
			con = DriverManager.getConnection(connectStr, uname, pwd);
		} catch (ClassNotFoundException e) {
			System.out.println("未找到jdbc驱动, 请查看项目文件夹是否被更改!");
			return false;
		} catch (SQLException e) {
			System.out.println("连接: " + connectStr + "数据库失败, 请检查网路连接.");
			return false;
		}
		return true;
	}

	/**
	 * 执行存储过程
	 * 
	 * @param 存储过程名称
	 * @param 参数列表
	 * @return
	 * @author 简航 创建日期 2009-08-20 16:03:20
	 */
	public static List<Map<String, Object>> callStatement(String name, List<Object> parm, boolean isResult, Boolean isCloseConn) {
		String st = "{call " + name + "(";
		for (int i = 0; i < parm.size(); i++)
			st += "?,";
		st = st.substring(0, st.length() - 1) + ")}";
		
		CallableStatement callsta = null;
		ResultSet rs = null;
		List<Map<String, Object>> result = null;
		try {
			callsta = con.prepareCall(st);
			
			for (int i = 0; i < parm.size(); i++) {
				if (parm.get(i) == null) {
					callsta.setObject(i + 1, null);
					continue;
				}
				if ("java.lang.Integer".equals(parm.get(i).getClass().getName())) {
					callsta.setInt(i + 1, Integer.parseInt(parm.get(i).toString()));
					continue;
				}
				if ("java.lang.String".equals(parm.get(i).getClass().getName())) {
					callsta.setString(i + 1, parm.get(i).toString());
					continue;
				}
				if ("java.lang.Float".equals(parm.get(i).getClass().getName())) {
					callsta.setFloat(i + 1, Float.parseFloat(parm.get(i).toString()));
					continue;
				}
				if ("java.lang.Long".equals(parm.get(i).getClass().getName())) {
					callsta.setLong(i + 1, Long.parseLong(parm.get(i).toString()));
					continue;
				}
				if ("java.util.Date".equals(parm.get(i).getClass().getName())) {
					callsta.setDate(i + 1, Date.valueOf(DateUtils.getDateStr((java.util.Date)parm.get(i), "yyyy-MM-dd")));
					continue;
				}
				if ("java.sql.Timestamp".equals(parm.get(i).getClass().getName())) { 
					callsta.setTimestamp(i + 1, (Timestamp)parm.get(i));
					continue;
				}
			}
			if (isResult) {
				rs = callsta.executeQuery();
				result = rsToArray(rs);
				rs.close();
			} else {
				callsta.execute();
			}
			
			callsta.close();
			
			if (isCloseConn != null && isCloseConn)
				closeConnection();
		} catch (SQLException e) {
			try {
				if (rs != null)
					rs.close();
				callsta.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			closeConnection();
			System.out.println("执行存储过程时发生错误: " + e.getMessage());
		}
		closeConnection();
		return result;
	}

	/**
	 * 执行增/删/改语句
	 * @param 要执行的sql语句
	 * @param 要执行的sql语句
	 * @return 影响的条数
	 * @author 简航 创建日期 2009-08-20 15:03:19
	 */
	public static int execSQL(String sql, Boolean isCloseConn) {
		int result = 0;
		try {
			result = con.prepareStatement(sql).executeUpdate();
		} catch (SQLException e) {
			System.out.println("执行语句: [" + sql + "] 时发生异常: " + e.getMessage());
		}
		if (isCloseConn != null && isCloseConn)
			closeConnection();
		return result;
	}

	/**
	 * 根据sql语句查询(调用该方法后, 不需要调用closeConnection)
	 * 
	 * @param sql查询语句
	 * @return 查询结构(已封装为集合框架)
	 * @author 简航 创建日期 2009-08-20 14:59:47
	 */
	public static List<Map<String, Object>> queryReturnMapInList(String sql, Boolean isCloseConn) {
		List<Map<String, Object>> list = null;

		try {
			PreparedStatement stsm = con.prepareStatement(sql);
			ResultSet rs = stsm.executeQuery();

			list = rsToArray(rs);

			rs.close();
			stsm.close();
			if (isCloseConn != null && isCloseConn)
				closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return list;
	}

	public static List<Map<String, Object>> rsToArray(ResultSet rs) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			ResultSetMetaData metaData = rs.getMetaData();
			String[] headList = new String[metaData.getColumnCount()];
			for (int i = 0; i < metaData.getColumnCount(); i++) {
				headList[i] = metaData.getColumnName(i + 1);
			}
			Map<String, Object> map = null;
			while (rs.next()) {
				map = new HashMap<String, Object>();
				for (int i = 0; i < headList.length; i++) {
					map.put(headList[i], (rs.getObject(i + 1) != null ? rs
							.getString(i + 1) : ""));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return list;
	}

	/**
	 * 关闭连接, 执行完任何非数据库查询操作后必须调用的方法
	 * 
	 * @return
	 * @author 简航 创建日期 2009-08-20 14:58:38
	 */
	public static boolean closeConnection() {
		try {
			if (!con.isClosed())
				con.close();
		} catch (SQLException e) {
			System.out.println("关闭数据库连接时, 发生异常: " + e.getMessage());
			return false;
		}
		return true;
	}

	/**
	 * 查询辅助方法
	 * @param 分页对象
	 * @param 表名
	 * @param 排序字符串
	 * @param 条件字符串
	 * @param 子查询字符串
	 * @return 是否查询成功
	 * @author 简航
	 * 创建日期 2009-09-23 14:57:45
	 */
	public static boolean findToPage(PageBean pageBean, String table,
			String orderStr, String term, String sonSelectStr, Boolean isCloseConn) {
		String sql = "SELECT t_table.* FROM (SELECT *," + (sonSelectStr != null ? sonSelectStr + Sys.COMMA : "") +
				" ROW_NUMBER() OVER(ORDER BY "
				+ orderStr + ") AS rowNum FROM " + table + " t1 WHERE 1=1 "
				+ (term != null && !"".equals(term) ? term : "")
				+ ") AS t_table WHERE t_table.rowNum BETWEEN "
				+ ((pageBean.getPage() - 1) * pageBean.getPageSize() + 1) + " AND "
				+ pageBean.getPage() * pageBean.getPageSize() + " ORDER BY "
				+ orderStr;
		
		try {
			pageBean.setResultList(queryReturnMapInList(sql, false));
	
			if (pageBean.getResultList().size() > 0) {
				String countSql = "SELECT COUNT(*) AS S_COUNT FROM " + table + " t1 WHERE 1=1 "
						+ (term != null && !"".equals(term) ? term : "");
				pageBean.setTotal(Long.parseLong(findOneResult(countSql, false).toString()));
			}
			pageBean.compute();
			if (isCloseConn != null && isCloseConn)
				closeConnection();
			return true;
		} catch(Exception e) {
			System.out.println("分页查询, 封装PageBean时发生异常: " + e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * 执行结果集只有一个结果的查询语句
	 * @param 查询语句
	 * @return 结果
	 * @author 简航
	 * 创建日期 2009-12-01 16:13:33
	 */
	public static String findOneResult(String sql, Boolean isCloseConn) {
		String result = null;
		PreparedStatement stsm;
		try {
			stsm = con.prepareStatement(sql);
			ResultSet rs = stsm.executeQuery();
			if (rs.next())
				result = rs.getString(1);
			
			rs.close();
			stsm.close();
			if (isCloseConn != null && isCloseConn)
				closeConnection();
		} catch (SQLException e) {
			System.out.println("执行单结果查询时发生异常: " + e.getMessage());
		}
		return result;
	}
	
	@SuppressWarnings("rawtypes")
	public static DBConnCfg getHibernateCfg() {
		if (hibernateCfi == null) {
			hibernateCfi = new DBConnCfg();
			
			Document dataDoc = XmlUtils.loadXML(DatabaseUtils.class
					.getResourceAsStream("/../spring-servlet.xml"));
	
			if (dataDoc == null) {
				System.out.println("配置文件加载失败!");
				return null;
			}
			Element element = dataDoc.getRootElement();
			Element pei = null;
			for (Iterator pe = element.elementIterator(); pe.hasNext();) {
				pei = (Element) pe.next();
				if (null != pei.attributeValue("id") && 
							pei.attributeValue("id").equals("dataSource"))
					break;
			}
			
			for (Iterator pe = pei.elementIterator(); pe.hasNext();) {
				Element sonPe = (Element) pe.next();
				if (sonPe.attributeValue("name").equals("driver")) {
					hibernateCfi.setDriver(sonPe.attributeValue("value"));
				}
				if (sonPe.attributeValue("name").equals("driverUrl")) {
					hibernateCfi.setConStr(sonPe.attributeValue("value"));
				}
				if (sonPe.attributeValue("name").equals("user")) {
					hibernateCfi.setUname(sonPe.attributeValue("value"));
				}
				if (sonPe.attributeValue("name").equals("password")) {
					hibernateCfi.setPassword(sonPe.attributeValue("value"));
				}
			}
		}
		
		return hibernateCfi;
	}
}
